/**
 * 
 */
package util;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Course;
import model.Coursetype;
import model.Department;
import model.Score;
import model.Special;
import model.Student;
import model.Studentcourse;
import model.Teacher;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/** 
 * @作者 Zouzr 
 * @创建日期 Dec 17, 2008 
 * @版本 V 1.0 
 */

public class ExportScore 
{

	public static String exportScore(List scorelist,String targetDirectory)
	{
		try
		{
	      File file  = new File(targetDirectory+"/score.xls");
	      
	      WritableWorkbook wwb = Workbook.createWorkbook(file);

	      WritableSheet sheet = wwb.createSheet("studentscore", 1);
	      
	     
	       WritableFont fontBold= new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD);
	       WritableFont fontNormal= new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD);
	       WritableCellFormat cellFormat=new WritableCellFormat(fontBold);
	       cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
	       cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	       cellFormat.setWrap(true);
	       
	       WritableCellFormat cellDataFormat=new WritableCellFormat(fontNormal);       
	       cellDataFormat.setAlignment(jxl.format.Alignment.CENTRE);
	       cellDataFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	       cellDataFormat.setWrap(true);			
	       //sheet.

	
	      for (int i = sheet.getRows(); i < scorelist.size()+10; i++) 
	      {
	    	  sheet.insertRow(i);
	      }
	      
	      for (int i = sheet.getColumns(); i < 18; i++)
	      {
	    	  sheet.insertColumn(i);
	      }
	      
	      Label content=null;
	      Number number=null;
	      String title="学生学号,学生姓名,学生学院,学生专业,学生性别,学生邮箱,学生联系电话,学生家庭住址,教师姓名,教师性别,教师邮箱,教师联系电话,教师职务,教师家庭住址,课程,课程类别,平时分,考试分,总分,学期";
	      String titles[]=title.split(",");
	      
	      int k=0;
	      for(;k<titles.length;k++){
		      content=new Label(k,0,titles[k]);
		      content.setCellFormat(cellFormat);
		      sheet.addCell(content);
	      }
	      	    
	
	      sheet.setRowView(0,500);
	      sheet.setRowView(1, 500);
	      
	      //sheet.setRowView(0, 10);
	      //sheet.setRowView(1, 10);

	      
	      cellFormat.setFont(fontNormal);

	      
	      
	      for(int n=1;n<scorelist.size()+1;n++)
	      {
	    	  System.out.println("循环--------------------"+n);
	    	    Object[] obj=(Object[])scorelist.get(n-1);
	    	  	Score sc = (Score)obj[0];
            	Studentcourse stco = (Studentcourse)obj[1];
            	Student st = (Student)obj[2];
            	Course co = (Course)obj[3];
            	Teacher te = (Teacher)obj[4];
            	Special sp = (Special)obj[5];
            	Department de = (Department)obj[6];
            	Coursetype coty = (Coursetype)obj[7];
            	
	    	     content=new Label(0,n,st.getStunumber());
	    	     content.setCellFormat(cellDataFormat);
				  sheet.addCell(content); 
	    	    	 
	    	    
		    	  content=new Label(1,n,st.getStuname());
				  content.setCellFormat(cellDataFormat);
				  sheet.addCell(content); 
				  
			   
	    		  content=new Label(2,n,de.getDepartmentname());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);   
			      
			      
	    		  content=new Label(3,n,sp.getSpecialname());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      
			      content=new Label(4,n,st.getStusex());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			        
			      content=new Label(5,n,st.getStuemail());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			     
	    		  content=new Label(6,n,st.getStutelephone());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			     
	    		  content=new Label(7,n,st.getStuaddress());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);

			      
			      
	    		  content=new Label(8,n,te.getTeachername());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			     
	    		  content=new Label(9,n,te.getTeachersex());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(10,n,te.getTeacheremail());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(11,n,te.getTeachertelephone());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(12,n,te.getTeacherposition());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			     
			      content=new Label(13,n,te.getTeacheraddress());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(14,n,co.getCoursename());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);

			      content=new Label(15,n,coty.getCoursetype());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(16,n,sc.getPingshifen().toString());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(17,n,sc.getKaoshifen().toString());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      float total=sc.getPingshifen()+sc.getKaoshifen();
			      content=new Label(18,n,""+total);
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
			      content=new Label(19,n,stco.getXueqi());
			      content.setCellFormat(cellDataFormat);
			      sheet.addCell(content);
			      
		System.out.println("成功插入了一行数据-------------------------"+n);
			      sheet.setColumnView(6, 30);
			      sheet.setColumnView(5, 30);
			      sheet.setColumnView(4, 30);
			      sheet.setColumnView(3, 20);
			      sheet.setColumnView(2, 35);
			      sheet.setColumnView(0, 25);

	     
	      	   
			   
		}
	      	wwb.write();
	      	wwb.close();
		}
		catch(Exception ioe)
		{
			
		}
		return "score.xls";
	}
}
